from datetime import datetime

import requests
import xlrd
from requests_toolbelt import MultipartEncoder

from apps.db import db
from apps.app import app
from apps.model import BankSideInfo, BankSideExtendInfo, BorrowerInfo
from apps.seleuinms.process import get_borrower_spouse_info, get_house_info, get_borrower_info
from apps.seleuinms.requests.YzyhRequest import YzyhRequest
from apps.seleuinms.utils.request_utils import parse_response, get_headers

doc = xlrd.open_workbook('./市区一手房台账.xlsx')

table = doc.sheet_by_index(0)

print(table.nrows)
print(table.ncols)

rows = table.nrows


def curtime():
    return datetime.now().strftime('%Y-%m-%d %H:%M:%S')


def get_time_str(value):
    return datetime(*xlrd.xldate_as_tuple(value, 0)).strftime('%Y-%m-%d')


def process(inx):
    line = table.row(inx)
    # 一手房
    if line[2].value == '个人一手房公转商贴息贷款':
        return
    # 结清
    if line[17].value == 0 or line[54].value != '未结清':
        return
    info = BankSideInfo()
    info.contract_no = line[7].value
    info.custom_name = line[9].value
    info.baseWork_flow_name = '个人一手房贷款'
    info.start_org_code = line[0].value
    info.option_user_name = line[43].value
    info.description = line[2].value
    info.create_time = curtime()
    info.state = 'confirmed'

    # 查询个人客户管理
    if not YzyhRequest.cookie_dict:
        YzyhRequest.login()

    url = 'http://22.230.57.12:8080/cusPersonal/findPersonalList'
    data = {
        'custName': info.custom_name,
        'certType': '10',
        'certNo': line[10].value,
        'pageNum': '1',
        'pageSize': '10',
    }
    m = MultipartEncoder(data)
    headers = get_headers(m.content_type)
    res = requests.post(url, cookies=YzyhRequest.cookie_dict, data=m, headers=headers)
    res = parse_response(res)
    if res.get('data').get('certType') is not None:
        data = {
            'custName': info.custom_name,
            'certType': res.get('data').get('certType'),
            'certNo': line[10].value,
            'pageNum': '1',
            'pageSize': '10',
        }
        m = MultipartEncoder(data)
        headers = get_headers(m.content_type)
        res = requests.post(url, cookies=YzyhRequest.cookie_dict, data=m, headers=headers)
        res = parse_response(res)
    data = res.get('data').get('grid').get('list')[0]
    info.cust_id = data.get('custId')

    url = 'http://22.230.57.12:8080/theBankCredit/theBankLoanList'
    data = {
        'custId': info.cust_id,
        'pageNum': '1',
        'pageSize': '10',
    }
    m = MultipartEncoder(data)
    headers = get_headers(m.content_type)
    res = requests.post(url, cookies=YzyhRequest.cookie_dict, data=m, headers=headers)
    res = parse_response(res)
    data = res.get('data').get('grid').get('list')

    extend_info = BankSideExtendInfo()
    # 查询当前客户的贷款合同信息
    # 查询合同详情
    extend_info.contract_id = info.id
    extend_info.dyqr_tel = '15862011018'
    extend_info.contract_start_date = get_time_str(line[19].value)
    extend_info.contract_end_date = get_time_str(line[20].value)
    extend_info.total_price = line[39].value
    extend_info.contract_amt = line[16].value
    extend_info.is_combo = '0'
    extend_info.qlr = line[9].value
    extend_info.qlr_cert_no = line[10].value
    extend_info.dy_method = '一般抵押'
    extend_info.loan_method = '商业'

    for item in data:
        if item.get('loanContractNo') == info.contract_no:
            extend_info.contract_amt = item.get('loanAmt')
            info.business_key = item.get('loanApplyId')
            query = {
                'businessKey': info.business_key
            }
            borrower_info = get_borrower_info({}, YzyhRequest.cookie_dict, {'custId': info.cust_id})
            borrower_info = borrower_info.get('data').get('borrowerInfo')
            borrower_info = {
                # 义务人类型： main: 借款人 sub: 借款人配偶
                'type': 'main',
                # 联系方式
                "telphone": borrower_info.get('contTelContact'),
                'name': borrower_info.get('custName'),
                'no': borrower_info.get('custNo'),
                # 身份证上的地址
                'address': borrower_info.get('hsRegAddr'),
                'cust_id': borrower_info.get('custId'),
                # 证件类型：
                'cert_type': borrower_info.get('certType'),  # '10', # 身份证
                # 证件号：
                'cert_no': borrower_info.get('certNo'),
                'contract_id': info.id,
            }
            BorrowerInfo(**borrower_info).save()
            # 查询配偶信息
            res = get_borrower_spouse_info(query, YzyhRequest.cookie_dict, {'custId': info.cust_id})
            if res and res.get('data') is not None and res.get('data').get():
                """表示有公共义务人"""
                borrower_spouse_info = res.get('data').get('borrowerSpouseInfo')
                if borrower_info.get('custId') is not None:
                    borrower_spouse_info = {
                        # 义务人类型： main: 借款人 sub: 借款人配偶
                        'type': 'sub',
                        # 联系方式
                        "telphone": borrower_spouse_info.get('contTelContact'),
                        'name': borrower_spouse_info.get('custName'),
                        'no': borrower_spouse_info.get('custNo'),
                        # 身份证上的地址
                        'address': borrower_spouse_info.get('hsRegAddr'),
                        'cust_id': borrower_spouse_info.get('custId'),
                        # 证件类型：
                        'cert_type': borrower_spouse_info.get('certType'),  # '10', # 身份证
                        # 证件号：
                        'cert_no': borrower_spouse_info.get('certNo'),
                        'contract_id': info.id,
                    }
                    BorrowerInfo(**borrower_spouse_info).save()
            house_info = get_house_info({'businessKey': info.business_key}, YzyhRequest.cookie_dict)
            house_info = house_info.get('data').get('houseInfo')
            extend_info.house_located = house_info.get('collatLocationLoc')
            extend_info.house_whole_located = house_info.get('collatLocationLoc')
            extend_info.house_cert_no = house_info.get('houseOwnshCertNoSets')
            extend_info.total_price = house_info.get('houseTurnoverTotalPrice')
            break

    info.save()
    extend_info.save()
    db.session.commit()


def process_all():
    inx = 7997
    with app.app_context():
        while 1:
            process(inx)
            print('done ' + str(inx))
            inx += 1
            if inx > rows - 1:
                break


if __name__ == '__main__':
    with app.app_context():
        process(44)
